package com.physique.tea.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.physique.tea.Pojo.Material;
import org.apache.ibatis.annotations.*;

import java.util.List;
@Mapper
public interface MaterialMapper extends BaseMapper<Material> {
    // 增
    @Insert("INSERT INTO MATERIAL (ID, MATERIALNAME, MATERIALPRICES) VALUES (#{ID}, #{MATERIALNAME}, #{MATERIALPRICES})")
    @Options(useGeneratedKeys = true, keyProperty = "ID")
    int insertMaterial(Material material);

    // 删
    @Delete("DELETE FROM MATERIAL WHERE ID = #{ID}")
    int deleteById(@Param("ID") Integer ID);

    // 改
    @Update("UPDATE MATERIAL SET MATERIALNAME = #{MATERIALNAME}, MATERIALPRICES = #{MATERIALPRICES} WHERE ID = #{ID}")
    int updateMaterial(Material material);

    // 查
    @Select("SELECT ID, MATERIALNAME, MATERIALPRICES FROM MATERIAL WHERE ID = #{ID}")
    Material selectById(@Param("ID") Integer ID);

    @Select("SELECT ID, MATERIALNAME, MATERIALPRICES FROM MATERIAL WHERE MATERIALNAME LIKE '%' || #{NAME} || '%'")
    List<Material> searchByName(@Param("NAME") String NAME);

    @Select("SELECT ID, MATERIALNAME, MATERIALPRICES FROM MATERIAL WHERE MATERIALPRICES BETWEEN #{MIN} AND #{MAX} ORDER BY MATERIALPRICES")
    List<Material> selectByPriceRange(@Param("MIN") Double MIN, @Param("MAX") Double MAX);

    @Select("SELECT MATERIALNAME, MATERIALPRICES FROM MATERIAL ORDER BY MATERIALPRICES DESC LIMIT 10")
    List<Material> selectTop10Expensive();

    @Update("UPDATE MATERIAL SET MATERIALPRICES = #{PRICE} WHERE ID = #{ID}")
    int updatePrice(@Param("ID") Integer ID, @Param("PRICE") Double PRICE);
}